import json
import os
import sys
import time

import pandas as pd
import xlwt

from src.utils.LogUtils import Logger

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[1]
sys.path.append(rootPath)
from itertools import groupby
from tqdm import tqdm
from src.db.CcxDataCenterDb import CcxDataCenter

logger = Logger()


class RatingEntry:
    def __init__(self):
        # 打开数据库
        self.dataCenter = CcxDataCenter(logSql=False, autocommit=True)
        self.insertCount = 0
        self.deleteCount = 0
        self.updateCount = 0
        self.arg = sys.argv

    # 加载企业列表名单
    def initEnterpriseList(self):
        sql = """
              SELECT bond_name,short_name,secode,credit_code,trade_date ,interest_rate_spread  from base_bonds_interest_rate_spread_platform bbirsp 
            where trade_date in ('20230403')
                
             
        """
        return self.absSqlServer.execute_many(sql)

    # year:年份
    # ent_list:企业列表
    # totalEnt: 企业总数
    # balEnt: 已运算企业数量
    def process(self, sheet1, row, code, ratingUid, enterpriseItem):
        data = self.selectPlatformData(ratingUid)
        tqdm.write("加载数据：" + code)

        if data is not None:
            writeRow = 'A' + str(row)
            temp = []
            # tqdm.write("生成第" + writeRow + "行" + "数据：" + str(temp))
            sheet1.write_row(writeRow, temp)
            row = row + 1
            return row


headers = {
    "Content-Type": "application/json"
}

global balEnt
# 程序入口
if __name__ == "__main__":
    # 开始时间

    process = RatingEntry()

    enterprise_list = process.initEnterpriseList()
    # 根据行业字段分组
    data_grouped = {}
    for key, group in groupby(sorted(enterprise_list, key=lambda x: x['score_type']), lambda x: x['score_type']):
        data_grouped[key] = list(group)
    current_time = pd.datetime.now()
    formatted_date = current_time.strftime('%Y-%m-%d-%H-%M')
    excel_file = '产业QE评级数据' + str(formatted_date) + '.xlsx'

    if not os.path.exists(excel_file):
        xls = xlwt.Workbook()
        xls.add_sheet('产业QE数据导出')
        xls.save(excel_file)
    logger.info("文件名：" + excel_file)
    # 根据行业不同遍历
    for scoreTypeItem in tqdm(data_grouped, desc="processing:"):
        data = []
        dataByScoreType = data_grouped.get(scoreTypeItem)
        row = []
        logger.debug("start export" + scoreTypeItem)
        # 标题行
        rowTitle = ["债券名称", "债券简称", '年份', '经营卡总分', '财务PD']
        # 同行业遍历企业
        for item in dataByScoreType:
            dictItem = dict(item)
            row.append(dictItem.get('credit_code'))
            row.append(dictItem.get('level'))
            row.append(dictItem.get('year'))
            rating_result = dictItem.get('rating_result')
            if rating_result is not None and rating_result != '':
                rating_result_json = dict(json.loads(rating_result))
                oIndex = dict(rating_result_json.get('o_indx'))
                fIndex = dict(rating_result_json.get('f_indx'))
                # 追加打分卡总分
                amount = oIndex.get("amount", '')
                norm = fIndex.get("norm", '')
                row.append(amount)
                row.append(norm)

                for oIndexItem in oIndex:
                    oIndexLength = (len(oIndex) - 1) * 2
                    if len(rowTitle) < oIndexLength + 5:
                        rowTitle.append(oIndexItem)
                        rowTitle.append('得分')
                    if oIndexItem != 'amount':
                        indicator = dict(oIndex.get(oIndexItem))
                        # row.append(oIndexItem)
                        row.append(indicator.get('sum_value', ''))
                        row.append(indicator.get('score', ''))
            else:
                pass
            data.append(row)
            row = []
        logger.debug("export" + scoreTypeItem + "row tile" + str(rowTitle))
        data.insert(0, rowTitle)
        dataFrame = pd.DataFrame(data)
        with pd.ExcelWriter(excel_file, engine='openpyxl', mode='a') as writer:
            dataFrame.to_excel(writer, sheet_name=scoreTypeItem, index=False)
        logger.debug("export" + scoreTypeItem + "    insert end")
    logger.info("导出结束。")
